#####################################################################
# Product:      KnowledgeEngine (Plone Edition)
#
# File:         MySQLAssessmentStorageService.py
#
# Copyright:    Copyright (c) 2005, Faulkner Technologies
#
# Author:       Brian Oliver
#
# Description:  Defines an assessment storage service,
#               implementation based on the MySQL database
#               (using InnoDB tables)
#
#               NOTE: sql commands to create the appropriate
#               mysql database for this storage service
#               are located in the folder containing this file.
#
# See Also:     Assessment, Assessible, AssessmentStorageService
#               AssessmentComponentState, AssessmentAttribute,
#               AssessmentComponentValueTypes
#####################################################################

#####################################################################
## KnowledgeEngine Library Imports
from Products.KnowledgeEngine.Common import *
from Products.KnowledgeEngine.Core import *
from Products.KnowledgeEngine.Assessment import Assessment
from Products.KnowledgeEngine.AssessmentAttribute import AssessmentAttribute
from Products.KnowledgeEngine.AssessmentAttributeHandle import AssessmentAttributeHandle
from Products.KnowledgeEngine.AssessmentComponentState import AssessmentComponentState
from Products.KnowledgeEngine.AssessmentComponentValueTypes import *
from Products.KnowledgeEngine.AssessmentStorageService import AssessmentStorageServiceInfo, AssessmentStorageService
from Products.KnowledgeEngine.KERLProcessor import *
from Products.KnowledgeEngine.User import User
from Products.KnowledgeEngine.Caching import RequestCache
from Products.KnowledgeEngine.UserProviderService import UserProviderService

#####################################################################
## Zope Library Imports
from Globals import DTMLFile
from ImageFile import ImageFile
from DateTime import DateTime
 
#####################################################################
## Python Library Imports
import sys
import time

#####################################################################
## Python Logging Configuration
import logging
log = logging.getLogger("KnowledgeEngine.StorageService")

#---------------------------------------------------------------#

#MYSQL Constants
DATETIME_FORMAT = "%Y-%m-%d %H:%M:%S"

#####################################################################
## MySQLAssessmentStorageServiceInfo Class Definition
class MySQLAssessmentStorageServiceInfo(AssessmentStorageServiceInfo):
    """
        Defines the assessment storage service meta-information
    """
    
    #---------------------------------------------------------------#
    
    def getClassName(self):
        """
            Returns the name of the class for which 
            we are providing information.
        """
        return 'MySQLAssessmentStorageService'


    #---------------------------------------------------------------#

    def getDisplayName(self):
        """
            Return a friendly name for the class instances
            (this is used for displaying in lists etc)
        """
    
        return "KnowledgeEngine: MySQL Assessment Storage Service"

#####################################################################
## MySQLAssessmentStorageService Class Definition
class MySQLAssessmentStorageService(AssessmentStorageService):
    """
        Defines a tool that may be used to manage the storage of 
        assessments.
    """

    #---------------------------------------------------------------#

    #ABSTRACT TRANSIENT: a ServiceInfo implementation instance to provide info about this service
    _SERVICEINFO = MySQLAssessmentStorageServiceInfo()

    #---------------------------------------------------------------#

    def __init__(self):
        """
            Constructor for the object
        """
                
        #perform default initialisations
        AssessmentStorageService.__init__(self)
        
        #create the default properties (just the name of the mysql connection object)
        self._setProperty('dbConnectionName', '', 'string')


    #---------------------------------------------------------------#

    def getDatabaseConnection(self):
        """
            PRIVATE: Return a Zope Data Adapter (Connection object) to be
            used by this assessment storage service
        """

        starttime = time.clock()

        #attempt to locate the Zope Database Connection 
        zda = getattr(self, self.dbConnectionName)

        #get and return a real connection from the zda
        connection = zda()

        endtime = time.clock()

        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, "Fetched Database Connection in %f" % (endtime-starttime)))        
        
        return connection
        
    #---------------------------------------------------------------#
        
    def createAssessment(self, model, userIdentity = None, repositoryIdentity = None):
        """
            Creates and returns an new Assessment instance
            for the specified model and assessor.
            
            NOTE: Does not initially persist the assessment.  To 
            persist a new assessment you must call 'saveAssessment'
            on this interface
            
            RETURN TYPE: Assessment
        """
        
        return Assessment(generateIdentity("aid"), model, userIdentity, repositoryIdentity)
        
    #---------------------------------------------------------------#
        
    def saveAssessment(self, assessment):
        """
            Saves the specified assessment and updates
            it's current in-memory state (so that it isn't new or modified)
        """
        
        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()

        #is the assessment modified? (if so let's update the modified time)
        if assessment.getIsModified():
            assessment.setInstantModified(DateTime())
        
        #get the sql creation and modified time instants values
        instantCreated = assessment.getInstantCreated().strftime(DATETIME_FORMAT)
        instantModified = assessment.getInstantModified().strftime(DATETIME_FORMAT)
        
        #determine who the assessment is checkedoutby
        if assessment.getCheckedOutBy() is None:
            checkedOutBy = 'null'
        else:
            checkedOutBy = "'%s'" % assessment.getCheckedOutBy()
        
        #determine assessment credit transaction identity
        if assessment.getCreditTransactionIdentity() is None:
            creditTransactionIdentity = 'null'
        else:
            creditTransactionIdentity = "'%s'" % assessment.getCreditTransactionIdentity()

        #create or update the assessment
        if assessment.getIsNew():
            #create a new assessment
            sql = "INSERT INTO assessments (assessmentIdentity, modelIdentity, instantCreated, instantModified, createdBy, isLocked, name, checkedOutBy, creditTransactionIdentity) VALUES ('%s', '%s', '%s', '%s', '%s', %d, '%s', %s, %s)" % (assessment.getIdentity(), assessment.getModelIdentity(), instantCreated, instantModified, assessment.getCreatedBy(), assessment.getIsLocked(), assessment.getName(), checkedOutBy, creditTransactionIdentity) 
        
            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            dbConnection.query(sql)
            
        elif assessment.getIsModified():
            #update the assessment only if it is modified
            sql = "UPDATE assessments SET instantModified = '%s', createdBy = '%s', isLocked = %d, name = '%s', checkedOutBy = %s, creditTransactionIdentity = %s WHERE assessmentIdentity = '%s' AND modelIdentity = '%s'" % (instantModified, assessment.getCreatedBy(), assessment.getIsLocked(), assessment.getName(), checkedOutBy, creditTransactionIdentity, assessment.getIdentity(), assessment.getModelIdentity())
        
            #TODO: we could check here if a concurrent update has occured and bail out... but we won't.  we'd probably throw an exception if we found a problem here
        
            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            dbConnection.query(sql)
        
        #save all of the component state in the assessment (only if the assessment is modified)
        if assessment.getIsNew() or assessment.getIsModified():
        
            #save the component state
            for componentState in assessment.getComponentStates():
                #save the current component state using this db connection
                self.saveComponentState(componentState, assessment.getInstantModified(), dbConnection)

            #save the attributes
            for attribute in assessment.getAttributes():
                #save the current attribute using this db connection
                self.saveAttribute(attribute, assessment.getInstantModified(), dbConnection)

            #the assessment is no longer new or modified
            assessment.setIsNew(false)
            assessment.setIsModified(false)        
        
    #---------------------------------------------------------------#
        
    def loadAssessment(self, assessmentIdentity, model, userIdentity = None, repositoryIdentity = None):
        """
            Loads the assessment with the specified
            assessment and model from storage.
            
            NOTE 1: Returns None if the assessment is unknown
            
            NOTE 2: Does not necessarily load all of the component
            state for all Assessible components in the model.
            
            RETURN TYPE: Assessment
        """        
        
        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()

        #determine if the assessment exists
        sql = "SELECT assessmentIdentity, modelIdentity, instantCreated, instantModified, createdBy, isLocked, name, checkedOutBy, creditTransactionIdentity FROM assessments WHERE assessmentIdentity = '%s' AND modelIdentity = '%s'" % (assessmentIdentity, model.getIdentity())
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))
        (columns, resultset) = dbConnection.query(sql)
        
        #did we find the assessment?
        if len(resultset) > 0:
            #get the assessment row
            values = resultset[0]
        
            #instantiate an Assessment and return it using the persistent values
            assessment = Assessment(assessmentIdentity, model, userIdentity, repositoryIdentity)            
            assessment.setInstantCreated(values[2])
            assessment.setInstantModified(values[3])
            assessment.setCreatedBy(values[4])
            assessment.setIsLocked(values[5] == 1)
            assessment.setName(values[6])
            assessment.setCheckedOutBy(values[7])
            assessment.setCreditTransactionIdentity(values[8])
            assessment.setIsNew(false)
            assessment.setIsModified(false)
            return assessment
            
        else:
            #not found, so return nothing
            return None
        
    #---------------------------------------------------------------#
        
    def checkoutAssessment(self, assessment, user):
        """
            Attempts to checkout the specified assessment
            for the specified model and user
            
            If successful, the checkedOutBy property of the returned
            assessment will equal user.getIdentity()
            
            Otherwise either the assessment is in use by another user
            or the model supports concurrent assessment.
            
            RETURN TYPE: Assessment
        """
        
        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()

        #does the model support concurrent assessment?
        if assessment.getModel().getPermitsConcurrentAssessment():
            assessment.setCheckedOutBy(None)
            
        else:
            #is the assessment new? (if so we don't need to check it out... we just set the checkout user)
            if assessment.getIsNew():
                assessment.setCheckedOutBy(user.getIdentity())
            else:
                #attempt to lock the assessment in the db (so we can set the user that is checking out the assessment) 
                #note: it appears as if zope does the locking for us... so there is no reason to use 'FOR UPDATE'
                sql = "SELECT checkedOutBy, instantModified FROM assessments WHERE assessmentIdentity = '%s' AND modelIdentity = '%s'" % (assessment.getIdentity(), assessment.getModel().getIdentity())
        
                #log the sql so we can see what is going on
                log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

                (columns, resultset) = dbConnection.query(sql)
                
                #did we find the assessment?
                if len(resultset) > 0:
                    #get the assessment row
                    values = resultset[0]

                    #who has the assessment checked out?
                    checkedOutBy = values[0]
                    lastModified = values[1]

                    #is the assessment checkedout by anyone or is it already checkedout by the specified user
                    if checkedOutBy is None or checkedOutBy == "" or checkedOutBy == user.getIdentity():
                        #we're going to check it out by the specified user
                        checkedOutBy = user.getIdentity()
                        assessment.setCheckedOutBy(user.getIdentity())                        
                        
                    else:
                        #it's already checked out by someone else...
                        #has the time since the other person last updated the assessment longer than the maximum checkout time?
                        if lastModified.lessThan(DateTime().timeTime() - (getMaximumAssessmentCheckoutTime() * 60)):
                            #the previous user's checkout time has expired...

                            #we're going to check it out by the specified user
                            checkedOutBy = user.getIdentity()
                            assessment.setCheckedOutBy(user.getIdentity())                        

                    #update the checked out status for the assessment
                    sql = "UPDATE assessments SET checkedOutBy = '%s' WHERE assessmentIdentity = '%s' AND modelIdentity = '%s'" % (checkedOutBy, assessment.getIdentity(), assessment.getModel().getIdentity())
                    dbConnection.query(sql)
                    
                else:
                    #not found, so return the assessment without changing the checkedOutBy property
                    pass
                    
        return assessment            
        
    #---------------------------------------------------------------#
        
    def checkinAssessment(self, assessment, user):
        """
            Attempts to checkin the specified assessment.
            
            If successful, the checkedOutBy property of the returned
            assessment will be None.  
            
            Otherwise the assessment is in use by another user or
            it supports concurrent assessment
            
            RETURN TYPE: Assessment
        """
            
        pass            
        
        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()

        #does the model support concurrent assessment?
        if assessment.getModel().getPermitsConcurrentAssessment():
            assessment.setCheckedOutBy(None)
            
        else:
            #is the assessment new? (if so we don't need to check it in...)
            if assessment.getIsNew():
                #nothing to do as not persistent yet
                pass
                
            else:
                #attempt to lock the assessment in the db (so we can set the user that is checking in the assessment) 
                #note: it appears as if zope does the locking for us... so there is no reason to use 'FOR UPDATE'
                sql = "SELECT checkedOutBy FROM assessments WHERE assessmentIdentity = '%s' AND modelIdentity = '%s'" % (assessment.getIdentity(), assessment.getModel().getIdentity())

                #log the sql so we can see what is going on
                log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

                (columns, resultset) = dbConnection.query(sql)
                
                #did we find the assessment?
                if len(resultset) > 0:
                    #get the assessment row
                    values = resultset[0]

                    #who has the assessment checked out?
                    checkedOutBy = values[0]

                    #is the assessment checkedout by anyone or is it already checkedout by the specified user
                    if checkedOutBy is None or checkedOutBy == "" or checkedOutBy == user.getIdentity():
                        #we're going to check it out by the specified user
                        checkedOutBy = 'null'
                        assessment.setCheckedOutBy(None)                        
                        
                    else:
                        #it's already checked out by someone else...
                        checkedOutBy = "'%s'" % checkedOutBy

                    #update the checked out status for the assessment
                    sql = "UPDATE assessments SET checkedOutBy = %s WHERE assessmentIdentity = '%s' AND modelIdentity = '%s'" % (checkedOutBy, assessment.getIdentity(), assessment.getModel().getIdentity())
                    dbConnection.query(sql)
                    
                else:
                    #not found, so return the assessment without changing the checkedOutBy property
                    pass
                    
        return assessment            
        
    #---------------------------------------------------------------#
        
    def deleteAssessmentWith(self, assessmentIdentity, modelIdentity):
        """
            Deletes the specified assessment from storage
            
            (including within repositories)
        """

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()

        #delete the model assessments from the assessments table
        sql = "DELETE FROM assessments WHERE assessmentIdentity = '%s' and modelIdentity = '%s'" % (assessmentIdentity, modelIdentity)
        dbConnection.query(sql)

        #delete the model assessments from the booleans table
        sql = "DELETE FROM booleans WHERE assessmentIdentity = '%s' and modelIdentity = '%s'" % (assessmentIdentity, modelIdentity)
        dbConnection.query(sql)
        
        #delete the model assessments from the numbers table
        sql = "DELETE FROM numbers WHERE assessmentIdentity = '%s' and modelIdentity = '%s'" % (assessmentIdentity, modelIdentity)
        dbConnection.query(sql)
        
        #delete the model assessments from the strings table
        sql = "DELETE FROM strings WHERE assessmentIdentity = '%s' and modelIdentity = '%s'" % (assessmentIdentity, modelIdentity)
        dbConnection.query(sql)
        
        #delete the model assessments from the repositories table
        sql = "DELETE FROM repositories WHERE assessmentIdentity = '%s' and modelIdentity = '%s'" % (assessmentIdentity, modelIdentity)
        dbConnection.query(sql)
        
    #---------------------------------------------------------------#

    def loadComponentState(self, assessment, componentIdentity, dbConnection = None):
        """
            ABSTRACT: Loads the component state for the specified
            component and returns the AssessmentComponentState.
            
            NOTE 1: Does not modify the assessment
            
            NOTE 2: Returns None if the component state doesn't exist
            or could not be loaded
        
            RETURN TYPE: AssessmentComponentState
        """
        
        #attempt to get the component from the component identity
        #(we do this so we can determine which table to look in)
        component = assessment.getModel().getComponent(componentIdentity)
        
        #did we find the component?
        if component is not None:
        
            #get the type of the component assessment state
            valueType = component.getAssessmentValueType()
            
            #let's assume we don't have to do any db work (we are very lazy now.  we only hit the db if we have too)
            sql = None

            #determine the query based on the component value type
            if valueType == st_BOOLEAN:
                sql = "SELECT assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value FROM booleans WHERE assessmentIdentity='%s' AND modelIdentity='%s' AND componentIdentity='%s'" % (assessment.getIdentity(), assessment.getModelIdentity(), componentIdentity)

            elif valueType == st_FLOAT or valueType == st_INTEGER:
                sql = "SELECT assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value FROM numbers WHERE assessmentIdentity='%s' AND modelIdentity='%s' AND componentIdentity='%s'" % (assessment.getIdentity(), assessment.getModelIdentity(), componentIdentity)

            elif valueType == st_STRING:
                sql = "SELECT assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value FROM strings WHERE assessmentIdentity='%s' AND modelIdentity='%s' AND componentIdentity='%s'" % (assessment.getIdentity(), assessment.getModelIdentity(), componentIdentity)

            else:
                raise RuntimeError, "Unsupported Assessment Component Value Type [%s] for the MySQLAssessmentStorageService" % (valueType,)
        
            #do we have any sql to perform of this component?
            if sql is not None:

                #get a database connection to work with (if we don't have one)
                if dbConnection is None:
                    dbConnection = self.getDatabaseConnection()

                #log the sql so we can see what is going on
                log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

                #execute the query 
                (columns, resultset) = dbConnection.query(sql)

                #did we find a result?
                if len(resultset) > 0:
                    #reconstruct the component state from the record
                    values = resultset[0]
                    
                    if valueType == st_BOOLEAN:
                        assessmentComponentState = AssessmentComponentState(assessment.getIdentity(), assessment.getModelIdentity(), componentIdentity, valueType, iif(values[5]==1, true, iif(values[5]==0, false, undefined)))

                    elif valueType == st_FLOAT:
                        assessmentComponentState = AssessmentComponentState(assessment.getIdentity(), assessment.getModelIdentity(), componentIdentity, valueType, float(values[5]))

                    elif valueType == st_INTEGER:
                        assessmentComponentState = AssessmentComponentState(assessment.getIdentity(), assessment.getModelIdentity(), componentIdentity, valueType, int(values[5]))

                    elif valueType == st_STRING:
                        assessmentComponentState = AssessmentComponentState(assessment.getIdentity(), assessment.getModelIdentity(), componentIdentity, valueType, values[5])
                    
                    #the component state is now stored
                    assessmentComponentState.setInstantCreated(values[3])
                    assessmentComponentState.setInstantModified(values[4])                    
                    assessmentComponentState.setIsNew(false)
                    assessmentComponentState.setIsModified(false)
                    return assessmentComponentState                
                    
                else:
                    return None
        
        else:
            #we assume that if the component is unknown, there is has no state!
            return None
        
    #---------------------------------------------------------------#

    def existsComponentState(self, assessment, componentIdentity, dbConnection = None):
        """
            Returns if persistent state exists for the
            specified component for the provided assessment.
            
            NOTE: This method checks the underlying persistent storage
            for the specified component's state.  It does not check 
            the 'cached' assessment for the component state.  That is,
            this method will return 'false' if the underlying 
            persistent storage does not contain the component state
            even if the provided assessment contains the component
            state.
            
            RETURN TYPE: Boolean
        """            

        #attempt to get the component from the component identity
        #(we do this so we can determine which table to look in)
        component = assessment.getModel().getComponent(componentIdentity)
        
        #did we find the component?
        if component is not None:
            #get the type of the component assessment state
            valueType = component.getAssessmentValueType()
            
            #determine the table to use
            if valueType == st_BOOLEAN:
                table = "booleans"
            elif valueType == st_FLOAT or valueType == st_INTEGER:
                table = "numbers"
            elif valueType == st_STRING:
                table = "strings"
            else:
                raise RuntimeError, "Unsupported Assessment Component Value Type [%s] for the MySQLAssessmentStorageService" % (valueType,)
        
            #sql to find the component state
            sql = "SELECT * FROM %s WHERE assessmentIdentity = '%s' and modelIdentity = '%s' and componentIdentity = '%s'" % (table, assessment.getIdentity(), assessment.getModelIdentity(), componentIdentity)
        
            #get a database connection to work with (if we don't have one)
            if dbConnection is None:
                dbConnection = self.getDatabaseConnection()

            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            #did we find any state?
            (columns, resultset) = dbConnection.query(sql)
            return len(resultset) > 0
        
        else:
            #we assume that if the component is unknown, there is has no state!
            return false
        
    #---------------------------------------------------------------#

    def deleteAllComponentState(self, component):
        """
           Deletes all of the component state for the
            specified component.
            
            This method is called when a component is deleted to
            clean up the assessment state for the said component
        """
        
        #get the type of the component assessment state
        valueType = component.getAssessmentValueType()

        #determine the table to use
        if valueType == st_BOOLEAN:
            table = "booleans"
        elif valueType == st_FLOAT or valueType == st_INTEGER:
            table = "numbers"
        elif valueType == st_STRING:
            table = "strings"
        else:
            raise RuntimeError, "Unsupported Assessment Component Value Type [%s] for the MySQLAssessmentStorageService" % (valueType,)

        #sql to find the component state
        sql = "DELETE FROM %s WHERE modelIdentity = '%s' and componentIdentity = '%s'" % (table, component.getModel().getIdentity(), component.getIdentity())

        #get a database connection to work with (if we don't have one)
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #delete the component state
        dbConnection.query(sql)
            
    #---------------------------------------------------------------#

    def saveComponentState(self, assessmentComponentState, instantModified = None, dbConnection = None):
        """
            Stores the specified assessment component state
            
            instantModified (optional) a DateTime instance that 
            is used to force the modification time of the component state
            to a specified value
            
            (generally called by self.saveAssessment)
        """
        
        #get the type of the component assessment state
        valueType = assessmentComponentState.getValueType()
    
        #let's assume we don't have to do any db work (we are very lazy now.  we only hit the db if we have too)
        sql = None
        
        #do we have a modification time?
        if instantModified is None:
            instantModified = DateTime()
        
        #set the new modification time if this component isn't new
        if not assessmentComponentState.getIsNew() and assessmentComponentState.getIsModified():
            assessmentComponentState.setInstantModified(instantModified)
        
        #get the sql creation and modified time instants 
        instantCreated  = assessmentComponentState.getInstantCreated().strftime(DATETIME_FORMAT)
        instantModified = assessmentComponentState.getInstantModified().strftime(DATETIME_FORMAT)

        #determine the query based on the component value type
        if valueType == st_BOOLEAN:
            #determine the persistent value
            value = iif(assessmentComponentState.getValue() == true, 1, iif(assessmentComponentState.getValue() == false, 0, -1)) 
        
            if assessmentComponentState.getIsNew():
                sql = "INSERT INTO booleans (assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value) VALUES ('%s', '%s', '%s', '%s', '%s', %d)" % (assessmentComponentState.getAssessmentIdentity(), assessmentComponentState.getModelIdentity(), assessmentComponentState.getComponentIdentity(), instantCreated, instantModified, value)                
            elif assessmentComponentState.getIsModified():
                sql = "UPDATE booleans SET instantModified = '%s', value = %d WHERE assessmentIdentity = '%s' AND modelIdentity = '%s' AND componentIdentity = '%s'" % (instantModified, value, assessmentComponentState.getAssessmentIdentity(), assessmentComponentState.getModelIdentity(), assessmentComponentState.getComponentIdentity())
            
        elif valueType == st_FLOAT or valueType == st_INTEGER:
            if assessmentComponentState.getIsNew():
                sql = "INSERT INTO numbers (assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value) VALUES ('%s', '%s', '%s', '%s', '%s', %s)" % (assessmentComponentState.getAssessmentIdentity(), assessmentComponentState.getModelIdentity(), assessmentComponentState.getComponentIdentity(), instantCreated, instantModified, assessmentComponentState.getValue())                
            elif assessmentComponentState.getIsModified():
                sql = "UPDATE numbers SET instantModified = '%s', value = %s WHERE assessmentIdentity = '%s' AND modelIdentity = '%s' AND componentIdentity = '%s'" % (instantModified, assessmentComponentState.getValue(), assessmentComponentState.getAssessmentIdentity(), assessmentComponentState.getModelIdentity(), assessmentComponentState.getComponentIdentity())
            
        elif valueType == st_STRING:
            if assessmentComponentState.getIsNew():
                sql = "INSERT INTO strings (assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')" % (assessmentComponentState.getAssessmentIdentity(), assessmentComponentState.getModelIdentity(), assessmentComponentState.getComponentIdentity(), instantCreated, instantModified, toSQL(assessmentComponentState.getValue()))                
            elif assessmentComponentState.getIsModified():
                sql = "UPDATE strings SET instantModified = '%s', value = '%s' WHERE assessmentIdentity = '%s' AND modelIdentity = '%s' AND componentIdentity = '%s'" % (instantModified, toSQL(assessmentComponentState.getValue()), assessmentComponentState.getAssessmentIdentity(), assessmentComponentState.getModelIdentity(), assessmentComponentState.getComponentIdentity())
            
        else:
            raise RuntimeError, "Unsupported Assessment Component Value Type [%s] for the MySQLAssessmentStorageService" % (valueType,)

        #do we have any sql to perform of this component?
        if sql is not None:
            #get a database connection to work with (if we don't have one)
            if dbConnection is None:
                dbConnection = self.getDatabaseConnection()
        
            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            #execute the query 
            dbConnection.query(sql)

            #the component state is now stored
            assessmentComponentState.setIsNew(false)
            assessmentComponentState.setIsModified(false)
        
    #---------------------------------------------------------------#

    def loadAttribute(self, assessment, attributeHandle, dbConnection = None):
        """
            Loads the attribute for the specified
            component and returns the result as an 
            AssessmentAttribute representation.
            
            NOTE 1: Does not modify the assessment
            
            NOTE 2: Returns None if the attribute doesn't exist
            or could not be loaded
        
            RETURN TYPE: AssessmentComponentState
        """

        #determine the type of the attribute
        valueType = attributeHandle.getValueType()
            
        #let's assume we don't have to do any db work (we are very lazy now.  we only hit the db if we have too)
        sql = None

        #determine the query based on the attribute value type
        if valueType == st_BOOLEAN:
            sql = "SELECT assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value FROM booleans WHERE assessmentIdentity='%s' AND modelIdentity='%s' AND componentIdentity='%s'" % (assessment.getIdentity(), assessment.getModelIdentity(), attributeHandle.getAttributeIdentity())

        elif valueType == st_FLOAT or valueType == st_INTEGER:
            sql = "SELECT assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value FROM numbers WHERE assessmentIdentity='%s' AND modelIdentity='%s' AND componentIdentity='%s'" % (assessment.getIdentity(), assessment.getModelIdentity(), attributeHandle.getAttributeIdentity())

        elif valueType == st_STRING:
            sql = "SELECT assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value FROM strings WHERE assessmentIdentity='%s' AND modelIdentity='%s' AND componentIdentity='%s'" % (assessment.getIdentity(), assessment.getModelIdentity(), attributeHandle.getAttributeIdentity())

        else:
            raise RuntimeError, "Unsupported Assessment Attribute Value Type [%s] for the MySQLAssessmentStorageService" % (valueType,)

        #do we have any sql to perform for this attribute?
        if sql is not None:

            #get a database connection to work with (if we don't have one)
            if dbConnection is None:
                dbConnection = self.getDatabaseConnection()
        
            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            #execute the query 
            (columns, resultset) = dbConnection.query(sql)

            #did we find a result?
            if len(resultset) > 0:
                #reconstruct the attribute from the record
                values = resultset[0]

                if valueType == st_BOOLEAN:
                    assessmentAttribute = AssessmentAttribute(attributeHandle, assessment.getIdentity(), iif(values[5]==1, true, iif(values[5]==0, false, undefined)))

                elif valueType == st_FLOAT:
                    assessmentAttribute = AssessmentAttribute(attributeHandle, assessment.getIdentity(), float(values[5]))

                elif valueType == st_INTEGER:
                    assessmentAttribute = AssessmentAttribute(attributeHandle, assessment.getIdentity(), int(values[5]))

                elif valueType == st_STRING:
                    assessmentAttribute = AssessmentAttribute(attributeHandle, assessment.getIdentity(), values[5])

                assessmentAttribute.setInstantCreated(values[3])
                assessmentAttribute.setInstantModified(values[4])                    
                assessmentAttribute.setIsNew(false)
                assessmentAttribute.setIsModified(false)
                return assessmentAttribute                

            else:
                return None
        
    #---------------------------------------------------------------#

    def existsAttribute(self, assessment, attributeHandle, dbConnection = None):
        """
            Returns if persistent attribute state exists for the
            specified attribute in the provided assessment.
            
            NOTE: This method checks the underlying persistent storage
            for the specified attributes's state.  It does not check 
            the 'cached' assessment for the attribute.  That is,
            this method will return 'false' if the underlying 
            persistent storage does not contain the attribute
            even if the provided assessment contains the component
            state.
            
            RETURN TYPE: Boolean
        """            

        #determine the type of the attribute
        valueType = attributeHandle.getValueType()

        #determine the table to use
        if valueType == st_BOOLEAN:
            table = "booleans"
        elif valueType == st_FLOAT or valueType == st_INTEGER:
            table = "numbers"
        elif valueType == st_STRING:
            table = "strings"
        else:
            raise RuntimeError, "Unsupported Assessment Attribute Value Type [%s] for the MySQLAssessmentStorageService" % (valueType,)

        #sql to find the component state
        sql = "SELECT * FROM %s WHERE assessmentIdentity = '%s' and modelIdentity = '%s' and componentIdentity = '%s'" % (table, assessment.getIdentity(), assessment.getModelIdentity(), attributeHandle.getAttributeIdentity())

        #get a database connection to work with (if we don't have one)
        if dbConnection is None:
            dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #did we find the attribute?
        (columns, resultset) = dbConnection.query(sql)
        return len(resultset) > 0
        
    #---------------------------------------------------------------#

    def deleteAllAttributes(self, attributeHandle):
        """
            Deletes all of the attributes with the 
            specified attributeIdentity.
            
            This method is called when a GenericObject is deleted 
            (that uses attributes) to clean up the assessment storage
        """
        
        #determine the type of the attribute
        valueType = attributeHandle.getValueType()

        #determine the table to use
        if valueType == st_BOOLEAN:
            table = "booleans"
        elif valueType == st_FLOAT or valueType == st_INTEGER:
            table = "numbers"
        elif valueType == st_STRING:
            table = "strings"
        else:
            raise RuntimeError, "Unsupported Assessment Attribute Value Type [%s] for the MySQLAssessmentStorageService" % (valueType,)

        #sql to delete the attribute
        sql = "DELETE FROM %s WHERE modelIdentity = '%s' and componentIdentity = '%s'" % (table, attributeHandle.getModelIdentity(), attributeHandle.getAttributeIdentity())

        #get a database connection to work with (if we don't have one)
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #delete the attribute
        dbConnection.query(sql)
            
    #---------------------------------------------------------------#

    def saveAttribute(self, attribute, instantModified = None, dbConnection = None):
        """
            Stores the specified assessment attribute
            
            instantModified (optional) a DateTime instance that 
            is used to force the modification time of the attribute
            to a specified value
            
            (generally called by self.saveAssessment)
        """
        
        #get the type of the attribute
        valueType = attribute.getAttributeHandle().getValueType()
    
        #let's assume we don't have to do any db work (we are very lazy now.  we only hit the db if we have too)
        sql = None
        
        #do we have a modification time?
        if instantModified is None:
            instantModified = DateTime()
        
        #set the new modification time if this component isn't new
        if not attribute.getIsNew() and attribute.getIsModified():
            attribute.setInstantModified(instantModified)
        
        #get the sql creation and modified time instants 
        instantCreated  = attribute.getInstantCreated().strftime(DATETIME_FORMAT)
        instantModified = attribute.getInstantModified().strftime(DATETIME_FORMAT)

        #determine the query based on the attribute value type
        if valueType == st_BOOLEAN:
            #determine the persistent value
            value = iif(attribute.getValue() == true, 1, iif(attribute.getValue() == false, 0, -1)) 
        
            if attribute.getIsNew():
                sql = "INSERT INTO booleans (assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value) VALUES ('%s', '%s', '%s', '%s', '%s', %d)" % (attribute.getAssessmentIdentity(), attribute.getAttributeHandle().getModelIdentity(), attribute.getAttributeHandle().getAttributeIdentity(), instantCreated, instantModified, value)                
            elif attribute.getIsModified():
                sql = "UPDATE booleans SET instantModified = '%s', value = %d WHERE assessmentIdentity = '%s' AND modelIdentity = '%s' AND componentIdentity = '%s'" % (instantModified, value, attribute.getAssessmentIdentity(), attribute.getAttributeHandle().getModelIdentity(), attribute.getAttributeHandle().getAttributeIdentity())
            
        elif valueType == st_FLOAT or valueType == st_INTEGER:
            if attribute.getIsNew():
                sql = "INSERT INTO numbers (assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value) VALUES ('%s', '%s', '%s', '%s', '%s', %s)" % (attribute.getAssessmentIdentity(), attribute.getAttributeHandle().getModelIdentity(), attribute.getAttributeHandle().getAttributeIdentity(), instantCreated, instantModified, attribute.getValue())                

            elif attribute.getIsModified():
                sql = "UPDATE numbers SET instantModified = '%s', value = %s WHERE assessmentIdentity = '%s' AND modelIdentity = '%s' AND componentIdentity = '%s'" % (instantModified, attribute.getValue(), attribute.getAssessmentIdentity(), attribute.getAttributeHandle().getModelIdentity(), attribute.getAttributeHandle().getAttributeIdentity())
            
        elif valueType == st_STRING:
            if attribute.getIsNew():
                sql = "INSERT INTO strings (assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')" % (attribute.getAssessmentIdentity(), attribute.getAttributeHandle().getModelIdentity(), attribute.getAttributeHandle().getAttributeIdentity(), instantCreated, instantModified, toSQL(attribute.getValue()))                
            elif attribute.getIsModified():
                sql = "UPDATE strings SET instantModified = '%s', value = '%s' WHERE assessmentIdentity = '%s' AND modelIdentity = '%s' AND componentIdentity = '%s'" % (instantModified, toSQL(attribute.getValue()), attribute.getAssessmentIdentity(), attribute.getAttributeHandle().getModelIdentity(), attribute.getAttributeHandle().getAttributeIdentity())
            
        else:
            raise RuntimeError, "Unsupported Assessment Component Value Type [%s] for the MySQLAssessmentStorageService" % (valueType,)

        #do we have any sql to perform on this attribute?
        if sql is not None:
            #get a database connection to work with (if we don't have one)
            if dbConnection is None:
                dbConnection = self.getDatabaseConnection()
        
            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            #execute the query 
            dbConnection.query(sql)

            #the attribute is now stored
            attribute.setIsNew(false)
            attribute.setIsModified(false)
             
    #---------------------------------------------------------------#

    def deleteAssessmentsForModel(self, model):
        """
            Deletes all of the assessment state for 
            the specified model

            (including within repositories)
        """

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()

        #delete the model assessments from the assessments table
        sql = "DELETE FROM assessments WHERE modelIdentity = '%s'" % (model.getIdentity(), )
        dbConnection.query(sql)

        #delete the model assessments from the booleans table
        sql = "DELETE FROM booleans WHERE modelIdentity = '%s'" % (model.getIdentity(), )
        dbConnection.query(sql)
        
        #delete the model assessments from the numbers table
        sql = "DELETE FROM numbers WHERE modelIdentity = '%s'" % (model.getIdentity(), )
        dbConnection.query(sql)
        
        #delete the model assessments from the strings table
        sql = "DELETE FROM strings WHERE modelIdentity = '%s'" % (model.getIdentity(), )
        dbConnection.query(sql)
        
        #delete the model assessments from the repositories table
        sql = "DELETE FROM repositories WHERE modelIdentity = '%s'" % (model.getIdentity(), )
        dbConnection.query(sql)
            
    #---------------------------------------------------------------#

    def deleteAllAssessments(self):
        """
            Deletes all of the assessments that are 
            stored using this assessment storage tool.

            (including within repositories)
        """

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()

        #delete the assessments from the assessments table
        sql = "DELETE FROM assessments"
        dbConnection.query(sql)

        #delete the assessments from the booleans table
        sql = "DELETE FROM booleans"
        dbConnection.query(sql)
        
        #delete the assessments from the numbers table
        sql = "DELETE FROM numbers"
        dbConnection.query(sql)
        
        #delete the assessments from the strings table
        sql = "DELETE FROM strings"
        dbConnection.query(sql)
        
        #delete the assessments from the repositories table
        sql = "DELETE FROM repositories"
        dbConnection.query(sql)
                
    #---------------------------------------------------------------#

    def isAssessmentInRepository(self, assessment, repositoryIdentity):
        """
            Returns if the specified assessment is in the
            repository specified by the repository identity.
        """

        #query to find the assessment in the repository
        sql = "SELECT * FROM repositories WHERE assessmentIdentity = '%s' and modelIdentity = '%s' and repositoryIdentity = '%s'" % (assessment.getIdentity(), assessment.getModelIdentity(), repositoryIdentity)

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #did we find any state?
        (columns, resultset) = dbConnection.query(sql)
        return len(resultset) > 0

    #---------------------------------------------------------------#

    def addAssessmentToRepository(self, assessment, repositoryIdentity):
        """
            Adds the specified assessment to the
            repository with the specified identity.  If the assessment 
            is already in the repository, nothing happens.
        """

        if not self.isAssessmentInRepository(assessment, repositoryIdentity):
        
            #generate a joining time
            instantJoined  = DateTime().strftime(DATETIME_FORMAT)
        
            #sql to insert the assessment into the specified repository
            sql = "INSERT INTO repositories (assessmentIdentity, modelIdentity, repositoryIdentity, instantJoined) VALUES ('%s', '%s', '%s', '%s')" % (assessment.getIdentity(), assessment.getModelIdentity(), repositoryIdentity, instantJoined) 

            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            #get a database connection to work with 
            dbConnection = self.getDatabaseConnection()
            dbConnection.query(sql)
            
    #---------------------------------------------------------------#

    def removeAssessmentFromRepository(self, assessment, repositoryIdentity):
        """
            Removes the specified assessment from the repository
            with the specified identity.  If the assessment is not
            in the repository, nothing happens.
        """
        
        #sql to delete the assessment from the specified repository
        sql = "DELETE FROM repositories WHERE assessmentIdentity = '%s' and modelIdentity = '%s' and repositoryIdentity = '%s'" % (assessment.getIdentity(), assessment.getModelIdentity(), repositoryIdentity)

        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #delete the component state
        dbConnection.query(sql)
        
    #---------------------------------------------------------------#

    def removeAssessmentFromRepositories(self, assessment):
        """
            ABSTRACT: Removes the specified assessment from 
            all of the repository in which it is located.
            
            If the assessment is not in a repository, nothing happens.
        """
        
        #sql to delete the assessment from all of the repositories
        sql = "DELETE FROM repositories WHERE assessmentIdentity = '%s' and modelIdentity = '%s'" % (assessment.getIdentity(), assessment.getModelIdentity())

        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #delete the component state
        dbConnection.query(sql)
        
    #---------------------------------------------------------------#

    def removeAssessmentsFromRepository(self, model, repositoryIdentity):
        """
            Removes all assessments from the specified model 
            repository.
            
            Does nothing if the repository doesn't exist
        """
        
        #sql to delete the assessment from the specified repository
        sql = "DELETE FROM repositories WHERE modelIdentity = '%s' and repositoryIdentity = '%s'" % (model.getIdentity(), repositoryIdentity)

        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #delete the component state
        dbConnection.query(sql)
        
    #---------------------------------------------------------------#

    def getNrAssessmentsInRepository(self, model, repositoryIdentity):
        """
            Returns an the number of assessments in the 
            specified model and repository.
            
            RETURN TYPE: int
        """ 

        #query to count the assessments in the repository
        sql = "SELECT count(*) FROM repositories WHERE modelIdentity = '%s' and repositoryIdentity = '%s'" % (model.getIdentity(), repositoryIdentity)

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #return the number of assessments
        (columns, resultset) = dbConnection.query(sql)
        return resultset[0][0]
        
    #---------------------------------------------------------------#

    def getInstantAssessmentJoinedRepository(self, assessment, repositoryIdentity):
        """
            Return the instant in time (since the Epoc) when the assessment
            was added to the specified respository
            
            RETURNS: -1 if the assessment is not in the respository
            
            RETURN TYPE: int
        """ 

        #query to find the assessment in the repository
        sql = "SELECT instantJoined FROM repositories WHERE assessmentIdentity = '%s' and modelIdentity = '%s' and repositoryIdentity = '%s'" % (assessment.getIdentity(), assessment.getModelIdentity(), repositoryIdentity)

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #did we find any state?
        (columns, resultset) = dbConnection.query(sql)
        if len(resultset) > 0:
            return resultset[0][0]
        else:
            return -1
        
    #---------------------------------------------------------------#

    def getRepositoriesForAssessment(self, assessment):
        """
            Returns a list of repositoryIdentities
            in which the specified assessment is located.
        """
        #query to find the assessment in the repository
        sql = "SELECT repositoryIdentity FROM repositories WHERE assessmentIdentity = '%s'" % (assessment.getIdentity(), )

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #return the respositoryIdentities
        (columns, resultset) = dbConnection.query(sql)
        return [row[0] for row in resultset]
        
    #---------------------------------------------------------------#

    def getNrAssessmentsInRepository(self, model, repositoryIdentity):
        """
            Returns the number of assessments 
            for the specified repository
        """
        
        #query to count the number of assessments for a model
        sql = "SELECT count(assessmentIdentity) FROM repositories WHERE modelIdentity = '%s' AND repositoryIdentity = '%s'" % (model.getIdentity(), repositoryIdentity)

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #return the results
        (columns, resultset) = dbConnection.query(sql)        
        return resultset[0][0]
        
    #---------------------------------------------------------------#

    def getAssessmentsInRepository(self, model, repositoryIdentity, batchNr = None, batchSize = 30, sortBy = None):
        """
            Returns a list of [Assessment] 
            in the specified model and repository.
            
            NOTE: If a batch number is specified, only the specified
            'batchSize' number of assessments are returned.  Otherwise
            all repository assessments are returned.

            RETURN TYPE: [Assessment]
        """
        
        #query to find the assessments in the repository
        sql = "SELECT assessmentIdentity FROM repositories WHERE modelIdentity = '%s' and repositoryIdentity = '%s' ORDER BY instantJoined" % (model.getIdentity(), repositoryIdentity)
        
        #do we have a batch size?
        if batchNr is not None:
            sql = sql + (" LIMIT %d, %d" % (int(batchNr * batchSize), batchSize))

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #get assessments
        (columns, resultset) = dbConnection.query(sql, max_rows=0)        
        assessments = []
        for row in resultset:
            #get the assessment identity to load        
            assessmentIdentity = row[0]
            
            #load the assessment
            assessment = self.loadAssessment(assessmentIdentity, model)
            
            #add the assessment to the result (if it is known)
            if assessment is not None:
                assessments.append(assessment)
        
        return assessments
        
    #---------------------------------------------------------------#

    def getAssessmentsInRepositoryWithScope(self, model, repositoryIdentity, scope, authenticatedUserIdentity):
        """
            Returns the assessments for the specified repository with in the specified scope, possibly
            using the authenticatedUser object (User)
            
            Returns a list of assessments that have been created by users that are in the specified
            scope list that are also in the specified repository.
            
            Parameters:
            scope : [String] - Is a list of actorIdentities that created the assessments
            authenticatedUserIdentity : String - Is a knowledge engine user
        """

        #do we have a scope?
        if scope is None or scope == []:
            return []

        #are we after all assessments?
        elif '*' in scope:
            return self.getAssessmentsInRepository(model, repositoryIdentity)
            
        #we are after a specific set of assessments
        else:
            #TODO: This might be an inefficient way to search, but it works for now!
            #We may want to rearrange it in the first instance to look like this;
            #SELECT assessments.assessmentIdentity 
            #FROM assessments 
            #WHERE assessments.assessmentIdentity IN (SELECT repositories.assessmentIdentity FROM repositories WHERE repositories.repositoryIdentity = <repositoryIdentity>) 
            #      AND assessments.createdBy IN <userSet>
            
            #build a set of users to check for assessment ownership
            #(replace the 'authenticateduser' with the authenicatedUser identity)
            #(replace the 'authenticatedusergroup' with the users in the authenticated user's group)
            #(replace the 'authenticatedusermanagedgroup' with the users in the groups that authenticated user is managing)
            userSet = ""
            for actorIdentity in scope:
                if actorIdentity == 'authenticateduser':
                    if len(userSet) > 0:
                        userSet = userSet + ","
                    userSet = userSet + ("'%s'" % authenticatedUserIdentity)
                    
                elif actorIdentity == 'authenticatedusergroup' or actorIdentity == 'authenticatedusermanagedgroup':
                    #get the group provider service so we can determine the members of the group
                    groupProviderService = model.getGroupProviderService()
                    
                    #determine the groups from which to derive users                    
                    if actorIdentity == 'authenticatedusergroup':
                        #get the groups to which the user belongs
                        groups = groupProviderService.getGroupsForActor(authenticatedUserIdentity)                        
                    else:                    
                        #get the groups that the actor is managing
                        groups = groupProviderService.getGroupsManagedByActor(authenticatedUserIdentity)
                    
                    #add all of the users in each of the groups to the user set
                    for group in groups:
                        for userIdentity in groupProviderService.getUserIdentitiesInGroup(group.getIdentity()):
                            if len(userSet) > 0:
                                userSet = userSet + ","
                            userSet = userSet + ("'%s'" % userIdentity)
                                
                else:
                    if len(userSet) > 0:
                        userSet = userSet + ","
                    userSet = userSet + ("'%s'" % actorIdentity)
        
            #we only execute the query if we have a user set
            if len(userSet) > 0:
                #query to find assessments the assessments repository
                sql = "SELECT repositories.assessmentIdentity FROM repositories LEFT JOIN assessments ON repositories.assessmentIdentity = assessments.assessmentIdentity WHERE assessments.modelIdentity = '%s' AND repositories.repositoryIdentity = '%s' AND assessments.createdBy IN (%s)" % (model.getIdentity(), repositoryIdentity, userSet)

                #get a database connection to work with 
                dbConnection = self.getDatabaseConnection()
        
                #log the sql so we can see what is going on
                log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

                #get assessments
                (columns, resultset) = dbConnection.query(sql, max_rows=0)        
                assessments = []
                for row in resultset:
                    #get the assessment identity to load        
                    assessmentIdentity = row[0]

                    #load the assessment
                    assessment = self.loadAssessment(assessmentIdentity, model)

                    #add the assessment to the result (if it is known)
                    if assessment is not None:
                        assessments.append(assessment)
            else:
                #as there are no users in scope, there can be no assessments
                assessments = []
                
            return assessments
        
    #---------------------------------------------------------------#

    def isAssessmentInScope(self, assessment, scope, authenticatedUserIdentity):
        """
            Returns if the specified assessment is in the specified scope, possibly
            using the authenticatedUser object (User)

            RETURNS: Boolean
            
            Parameters:
            assessment : Assessment - An Assessment
            scope : [String] - Is a list of actorIdentities that created the assessments
            authenticatedUserIdentity : String - Is a knowledge engine user
        """

        #do we have a scope?
        if scope is None or scope == []:
            return false

        #are we after all assessments?
        elif '*' in scope:
            return true
            
        #we are after a specific set of assessments
        else:
            #TODO: This might be an inefficient way to search, but it works for now.  
            #SELECT COUNT(assessments.assessmentIdentity)
            #FROM assessments 
            #WHERE assessments.assessmentIdentity IN (SELECT repositories.assessmentIdentity FROM repositories WHERE repositories.repositoryIdentity = <repositoryIdentity>) 
            #      AND assessments.createdBy IN <userSet>
            
            #build a set of users to check for assessment ownership
            #(replace the 'authenticateduser' with the authenicatedUser identity)
            #(replace the 'authenticatedusergroup' with the users in the authenticated user's group)
            #(replace the 'authenticatedusermanagedgroup' with the users in the groups that authenticated user is managing)

            userSet = self.knowledgeengine_userproviderservice.getUserSetFromScope(scope, assessment.getModel(), authenticatedUserIdentity)

            #we only execute the query if we have a user set
            if len(userSet) > 0:

                #query to find assessments the assessments repository
                sql = "SELECT COUNT(assessmentIdentity) FROM assessments WHERE assessments.assessmentIdentity = '%s' AND assessments.modelIdentity = '%s' AND assessments.createdBy IN (%s)" % (assessment.getIdentity(), assessment.getModel().getIdentity(), userSet)

                #get a database connection to work with 
                dbConnection = self.getDatabaseConnection()
        
                #log the sql so we can see what is going on
                log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

                #get assessments
                (columns, resultset) = dbConnection.query(sql, max_rows=0)                    
                return resultset[0][0] > 0
                
            else:
                #as there are no users, there can't be any scope
                return false
                
    #---------------------------------------------------------------#

    def getNrAssessmentsForModel(self, model):
        """
            Returns the number of assessments 
            for the specified model (regardless of the repository)
        """
        
        #query to count the number of assessments for a model
        sql = "SELECT count(assessments.assessmentIdentity) FROM assessments WHERE assessments.modelIdentity = '%s'" % model.getIdentity()

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #return the results
        (columns, resultset) = dbConnection.query(sql)        
        return resultset[0][0]
        
    #---------------------------------------------------------------#

    def getAssessmentsForModel(self, model, batchNr = None, batchSize = 30, sortBy = None):
        """
            Returns the assessments for the specified
            model (regardless of the repository)
            
            NOTE: If a batch number is specified, only the specified
            'batchSize' number of assessments are returned.  Otherwise
            all assessments are returned.

            RETURN TYPE: [Assessment]
        """
        
        #query to find all assessments
        sql = "SELECT assessments.assessmentIdentity FROM assessments WHERE assessments.modelIdentity = '%s'" % model.getIdentity()
        
        #what are we sorting by?
        if sortBy == 'isLocked':
            sql = sql + " ORDER BY assessments.isLocked desc, assessments.instantModified desc" 
        elif sortBy == 'instantModified':
            sql = sql + " ORDER BY assessments.instantModified desc" 
        elif sortBy == 'instantCreated':
            sql = sql + " ORDER BY assessments.instantCreated" 
        elif sortBy == 'name':
            sql = sql + " ORDER BY assessments.name" 
        
        #do we have a batch size?
        if batchNr is not None:
            sql = sql + (" LIMIT %d, %d" % (int(batchNr * batchSize), batchSize))

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #get assessments
        (columns, resultset) = dbConnection.query(sql, max_rows=0)        
        assessments = []
        for row in resultset:
            #get the assessment identity to load        
            assessmentIdentity = row[0]
            
            #load the assessment
            assessment = self.loadAssessment(assessmentIdentity, model)
            
            #add the assessment to the result (if it is known)
            if assessment is not None:
                assessments.append(assessment)
        
        return assessments
        
    #---------------------------------------------------------------#

    def getNrAssessmentsForModelAndUser(self, model, user):
        """
            Returns the number of assessments 
            for the specified model and user (regardless of the repository)
        """
        
        #query to count the number of assessments for a model and user
        sql = "SELECT count(assessments.assessmentIdentity) FROM assessments WHERE assessments.modelIdentity = '%s' AND assessments.createdBy = '%s'" % (model.getIdentity(), user.getIdentity())

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #return the results
        (columns, resultset) = dbConnection.query(sql)        
        return resultset[0][0]
        
    #---------------------------------------------------------------#

    def getAssessmentsForModelAndUser(self, model, user, batchNr = None, batchSize = 30, sortBy = None):
        """
            ABSTRACT: Returns the assessments for the specified
            model and user (regardless of the repository)
            
            NOTE: If a batch number is specified, only the specified
            'batchSize' number of assessments are returned.  Otherwise
            all assessments are returned.

            RETURN TYPE: [Assessment]
        """
        
        #query to find the assessments for a model and user
        sql = "SELECT assessments.assessmentIdentity FROM assessments WHERE assessments.modelIdentity = '%s' AND assessments.createdBy = '%s'" % (model.getIdentity(), user.getIdentity())

        #what are we sorting by?
        if sortBy == 'isLocked':
            sql = sql + " ORDER BY assessments.isLocked desc, assessments.instantModified desc" 
        elif sortBy == 'instantModified':
            sql = sql + " ORDER BY assessments.instantModified desc" 
        elif sortBy == 'instantCreated':
            sql = sql + " ORDER BY assessments.instantCreated" 
        elif sortBy == 'name':
            sql = sql + " ORDER BY assessments.name" 
        
        #do we have a batch size?
        if batchNr is not None:
            sql = sql + " LIMIT %d, %d" % (int(batchNr * batchSize), batchSize)

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #get assessments
        (columns, resultset) = dbConnection.query(sql, max_rows=0)        
        assessments = []
        for row in resultset:
            #get the assessment identity to load        
            assessmentIdentity = row[0]
            
            #load the assessment
            assessment = self.loadAssessment(assessmentIdentity, model)
            
            #add the assessment to the result (if it is known)
            if assessment is not None:
                assessments.append(assessment)
        
        return assessments
        
    #---------------------------------------------------------------#

    def compileKERLtoSQL(self, expression, model, user):
        """
            PRIVATE: Attempts to compile a KERL expression (in object representation)
            into SQL.
        
            Returns "" if a where clause can't be generated
        
            RETURN TYPE: String (where clause)
        """
        
        #do we have a KERL expression?
        if expression != None:
        
            #get the operator from the expression
            operator = expression[0]

            #-------------------------------------------------------------
            #KERL operator? (any, all, none)
            #(we choose appropriate binary, infix and postfix operators
            if operator == "all":
                bopr = " AND "
                iopr = ""
                popr = ""

            elif operator == "any":
                bopr = " OR "
                iopr = ""
                popr = ""

            elif operator == "none":
                bopr = " AND "
                iopr = "!("
                popr = ")"
            
            #-------------------------------------------------------------
            #assessment operator?
            elif operator == "assessment":
                #createdby operator   
                if len(expression) == 3 and expression[1] == 'createdby':         
                    return "createdby = '%s'" % (expression[2],)

                #ischeckedout operator
                elif len(expression) == 2 and expression[1] == 'ischeckedout':         
                    return "assessments.checkedOutBy IS NOT NULL";

                # 20060530 [JPM] islocked/isnotlocked operators
                elif len(expression) == 2 and expression[1] == 'islocked':
                    return "assessments.isLocked=1";
                elif len(expression) == 2 and expression[1] == 'isnotlocked':
                    return "assessments.isLocked=0";
                
                elif len(expression) == 3 and expression[1] == 'inrepository':
                    return "assessmentIdentity IN (SELECT repositories.assessmentIdentity FROM repositories WHERE repositories.repositoryIdentity = '%s')" % (expression[2],)                    

                elif len(expression) == 3 and expression[1] == 'notinrepository':
                    #TODO: not supported yet
                    return ""

                elif len(expression) == 3 and expression[1] == 'onlyinrepository':                
                    #TODO: not supported yet
                    return ""

                elif len(expression) == 2 and expression[1] == 'onlyincurrentuserrepository':         
                    #TODO: not supported yet
                    return ""

                elif len(expression) == 2 and expression[1] == 'isorphaned':         
                    return "NOT EXISTS (SELECT * FROM repositories WHERE repositories.assessmentIdentity = assessments.assessmentIdentity)"

                #date operators
                elif len(expression) == 3 and expression[1] in ['createdbefore','createdafter','modifiedbefore','modifiedafter']:
                
                    #create a DateTime for the date
                    when = DateTime(expression[2])
                    
                    if expression[1] == 'createdbefore':
                        return "instantCreated < '%s'" % when.strftime(DATETIME_FORMAT)
                    elif expression[1] == 'createdafter':
                        return "instantCreated >= '%s'" % when.strftime(DATETIME_FORMAT)
                    if expression[1] == 'modifiedbefore':
                        return "instantModified < '%s'" % when.strftime(DATETIME_FORMAT)
                    elif expression[1] == 'modifiedafter':
                        return "instantModified >= '%s'" % when.strftime(DATETIME_FORMAT)
                    else:
                        #TODO: there is some problem with the expression.  we should throw an exception
                        return ""
                    
                else:
                    #TODO: there is some problem with the expression.  it's not supported!
                    return ""
            
            #-------------------------------------------------------------
            #a component operator?
            elif isComponentOperator(expression):
                #get the component 
                componentIdentity = expression[0]
                component = model.getComponent(componentIdentity)
                
                #handle each component as required
                #(we don't delegate this here based on component as it is storage specific!)
                #-------------------------------------
                if component.isInstanceOf("CheckBox"):

                    #determine the value we are looking for                    
                    if expression[1] == "selected":
                        value = true
                    elif expression[1] == "unselected":
                        value = false
                    else:
                        value = undefined

                    #generate the SQL where clause component
                    return "assessmentIdentity IN (SELECT assessmentIdentity FROM booleans WHERE modelIdentity = '%s' AND componentIdentity = '%s' AND value = %d)" % (model.getIdentity(), component.getIdentity(), value)                    

                #-------------------------------------
                elif component.isInstanceOf("Option"):

                    #determine the value we are looking for                    
                    if expression[1] == "selected":
                        value = true
                    elif expression[1] == "unselected":
                        value = false
                    else:
                        value = undefined

                    #generate the SQL where clause component
                    return "assessmentIdentity IN (SELECT assessmentIdentity FROM booleans WHERE modelIdentity = '%s' AND componentIdentity = '%s' AND value = %d)" % (model.getIdentity(), component.getIdentity(), value)                    

                #-------------------------------------
                elif component.isInstanceOf("MultipleChoice"):

                    #determine the value we are looking for                    
                    if expression[1] == "selected":
                        value = expression[2]
                        opr = "="
                        
                    #expression[1] == "unselected":
                    else:
                        value = expression[2]
                        opr = "<>"

                    #generate the SQL where clause component
                    return "assessmentIdentity IN (SELECT assessmentIdentity FROM strings WHERE modelIdentity = '%s' AND componentIdentity = '%s' AND value %s '%s')" % (model.getIdentity(), component.getIdentity(), opr, value)                    

                #-------------------------------------
                elif component.isInstanceOf("ScoredReport"):

                    #determine the value we are looking for                    
                    if expression[1] == "pass" or expression[1] == "fail":
                        if expression[1] == "pass":
                            value = component.getPassScore()
                            opr = ">="
                        else:
                            value = component.getPassScore()
                            opr = "<"

                        #generate the SQL where clause component
                        return "assessmentIdentity IN (SELECT assessmentIdentity FROM numbers WHERE modelIdentity = '%s' AND componentIdentity = '%s:score' AND value %s %f)" % (model.getIdentity(), component.getIdentity(), opr, value)                    

                    elif expression[1] == 'scoreinrange':
                        fromValue = float(expression[2])
                        toValue = float(expression[3])

                        #generate the SQL where clause component
                        return "assessmentIdentity IN (SELECT assessmentIdentity FROM numbers WHERE modelIdentity = '%s' AND componentIdentity = '%s:score' AND value >= %f AND value < %f)" % (model.getIdentity(), component.getIdentity(), fromValue, toValue)                    
                        
                    else:
                        #TODO: unknown/unsupported scored report type
                        return ""
                
                else:
                    #TODO: unknown/unsupported component type
                    return ""

            else:
                #unknown type or unsupported type of KERL expression
                return ""
        
            #-------------------------------------------------------------
            #process the KERL operator
            
            #a list of expressions to join
            compiledExpressions = []

            #iterate over each of the sub expressions, converting them to SQL expression as necessary
            for subexpression in expression[1:]:

                #compile the current sub expression
                #(may return "")
                compiledExpression = self.compileKERLtoSQL(subexpression, model, user)

                if compiledExpression != "":
                    compiledExpressions = compiledExpressions + [compiledExpression]

            #now build the result SQL from the compiled expressions
            #(don't return anything if we have no compiled expressions)
            if len(compiledExpressions) == 0:
                return ""
                
            #(return the expression itself if there is only one compiled expression)                
            elif len(compiledExpressions) == 1:
                return iopr + compiledExpressions[0] + popr
            
            #many sub expressions
            else:
                result = ""
                for compiledExpression in compiledExpressions:
                    #always add the binary operator after the first compiled expression
                    if result != "":
                        result = result + bopr
                        
                    result = result +  iopr + compiledExpression + popr  

                return "(" + result + ")"
        
        else:
            #no expression means no where clause
            return ""
        
    #---------------------------------------------------------------#

    def compileScopeToSQL(self, scope, model, user):
        """
            Attempts to compile the specified assessment scope
            into an appropriate SQL where clause to be used when filtering
            assessments.
            
            The parameter 'scope' is a list of actor identities (including groups)
            who (or whose members) have created a set of assessments.
            
            Additionally, the following actor identities may be used;
            (these are appropriately substituted when compiling the SQL)
            
            '*'                             : all portal members.
            'authenticateduser'             : the specified user.
            'authenticatedusergroup'        : the members of the specified user's group
            'authenticatedusermanagedgroup' : the members of the groups that the specified user is managing
                
            Specifying a scope of None or [] means None is returned.
            
            RETURNS:
                None    - if no scope was provided
                ""      - if ALL assessments are required
                String  - containing a SQL where clause condition 
                          representing the provide scope
        """

        #has any scope been provided?
        if scope is None or len(scope) == 0:
            return None

        #are we after all assessments?
        elif '*' in scope:    
            return ""
            
        #we are after a specific set of assessments
        else:
            #TODO: This might be an inefficient way to search, but it works for now!
            #We may want to rearrange it in the first instance to look like this;
            #SELECT assessments.assessmentIdentity 
            #FROM assessments 
            #WHERE assessments.assessmentIdentity IN (SELECT repositories.assessmentIdentity FROM repositories WHERE repositories.repositoryIdentity = <repositoryIdentity>) 
            #      AND assessments.createdBy IN <userSet>
            
            #build a set of users to check for assessment ownership
            #(replace the 'authenticateduser' with the authenicatedUser identity)
            #(replace the 'authenticatedusergroup' with the users in the authenticated user's group)
            #(replace the 'authenticatedusermanagedgroup' with the users in the groups that authenticated user is managing)
            #userSet = ""

            userSet = self.knowledgeengine_userproviderservice.getUserSetFromScope(scope, model, user)

            return "createdBy IN (%s)" % userSet    
        
    #---------------------------------------------------------------#

    def getNrAssessmentsSatisfyingFilter(self, assessmentFilter, user):
        """
            Returns the number of assessments that satisfy 
            the provided assessment filter and user
            
            NOTE: Does not take kerl transitivity into account or 
            showif rules that may cause values to be invalid.
            
            PARAMETERS:
            assessmentFilter : AssessmentFilter
            user : User - A Knowledge Engine User instance

            Assessment scoping provided as part of the AssessmentFilter.
            
            RETURN TYPE: NUMBER
        """            

        cache = RequestCache('getNrAssessmentsSatisfyingFilter', self.REQUEST)

        # Build a uniqe cache id
        modelIdentity = assessmentFilter.getModelIdentity()
        for singleScope in assessmentFilter.getAssessmentScope():
            singleScope += singleScope

        if type(user) == User:
            authenticatedUserIdentity = user.getIdentity()
        else:
            authenticatedUserIdentity = user

        expression = assessmentFilter.expression
        
        c_hash = `modelIdentity` + `expression` + `singleScope` + `authenticatedUserIdentity`

        if c_hash in cache:
            return cache[c_hash]

        #determine the scope SQL
        #get the model
        model = self.getModel(assessmentFilter.getModelIdentity())
        scopeWhereClause = self.compileScopeToSQL(assessmentFilter.getAssessmentScope(), model, user)

        #only proceed if we have a scope
        if scopeWhereClause is None:
            return 0
        else:
            #generate the SQL query
            sql = "SELECT COUNT(assessmentIdentity) FROM assessments"
            whereClause = self.compileKERLtoSQL(assessmentFilter.getExpression(), model, user)
            if len(whereClause) > 0:
                sql = sql + (" WHERE modelIdentity = '%s' AND " % model.getIdentity()) + whereClause
            else:
                sql = sql + (" WHERE modelIdentity = '%s'" % model.getIdentity())

            #add the scope where clause (if there is one)
            if len(scopeWhereClause) > 0:
                sql = sql + " AND " + scopeWhereClause            

            #get a database connection to work with 
            dbConnection = self.getDatabaseConnection()

            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            #return the results
            starttime = time.clock()
            (columns, resultset) = dbConnection.query(sql)        
            endtime = time.clock()
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, "SQL Execution Time: %f" % (endtime-starttime)))        

            cache[c_hash] = resultset[0][0]
            return resultset[0][0]
        
    #---------------------------------------------------------------#

    def getAssessmentsSatisfyingFilter(self, assessmentFilter, user, batchNr = None, batchSize = 30, sortBy = None):
        """
            Returns the assessments that satisfy the 
            provided assessment filter and user
            
            NOTE 1: Does not take kerl transitivity into account or 
            showif rules that may cause values to be invalid.
            
            NOTE 2: If a batch number is specified, only the specified
            'batchSize' number of assessments are returned.  Otherwise
            all assessments are returned that satisfy the filter.
            
            Assessment scoping provided as part of the AssessmentFilter.
            
            RETURN TYPE: [Assessment]
        """            

        #get the model
        model = self.getModel(assessmentFilter.getModelIdentity())

        #determine the scope SQL
        scopeWhereClause = self.compileScopeToSQL(assessmentFilter.getAssessmentScope(), model, user)

        #only proceed if we have a scope
        if scopeWhereClause is None:
            return []
            
        else:
            #generate the SQL query
            sql = "SELECT assessmentIdentity FROM assessments"
            whereClause = self.compileKERLtoSQL(assessmentFilter.getExpression(), model, user)
            if len(whereClause) > 0:
                sql = sql + (" WHERE modelIdentity = '%s' AND " % model.getIdentity()) + whereClause
            else:
                sql = sql + (" WHERE modelIdentity = '%s'" % model.getIdentity())

            #add the scope where clause (if there is one)
            if len(scopeWhereClause) > 0:
                sql = sql + " AND " + scopeWhereClause            

            #what are we sorting by?
            if sortBy == 'isLocked':
                sql = sql + " ORDER BY assessments.isLocked desc, assessments.instantModified desc" 
            elif sortBy == 'isOpen':
                sql = sql + " ORDER BY assessments.isLocked asc, assessments.instantModified desc" 
            elif sortBy == 'instantModified':
                sql = sql + " ORDER BY assessments.instantModified desc" 
            elif sortBy == 'instantCreated':
                sql = sql + " ORDER BY assessments.instantCreated" 
            elif sortBy == 'name':
                sql = sql + " ORDER BY assessments.name" 

            #do we have a batch size?
            if batchNr is not None:
                sql = sql + " LIMIT %d, %d" % (int(batchNr * batchSize), batchSize)

            #get a database connection to work with 
            dbConnection = self.getDatabaseConnection()

            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            #get assessments
            starttime = time.clock()
            (columns, resultset) = dbConnection.query(sql, max_rows=0)        
            endtime = time.clock()
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, "SQL Execution Time: %f" % (endtime-starttime)))        

            assessments = []
            for row in resultset:
                #get the assessment identity to load        
                assessmentIdentity = row[0]

                #load the assessment
                assessment = self.loadAssessment(assessmentIdentity, model)

                #add the assessment to the result (if it is known)
                if assessment is not None:
                    assessments.append(assessment)

            return assessments
        
    #---------------------------------------------------------------#
        
#####################################################################
## Class Initialisation
# (none)

# Register Service Information with the Knowledge Engine
registerServiceClass(MySQLAssessmentStorageService)

#####################################################################
